package com.gbase8c.dmt.db.oracle;

import com.alibaba.fastjson.JSON;
import com.gbase8c.dmt.db.meta.AbstractMeta;
import com.gbase8c.dmt.db.meta.ExtTypes;
import com.gbase8c.dmt.model.migration.config.Snapshot;
import com.gbase8c.dmt.model.migration.dto.DataSourceDto;
import com.gbase8c.dmt.model.migration.dto.RoleDto;
import com.gbase8c.dmt.model.migration.dto.UserDto;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.Types;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class MetaImpl extends AbstractMeta {

    private static List<String> dataTypes = Lists.newArrayList(
            "CHAR", "NCHAR", "VARCHAR2", "NVARCHAR2",
            "NUMBER", "FLOAT", "BINARY_FLOAT", "BINARY_DOUBLE",
            "LONG", "LONG RAW", "RAW",
            "DATE", "TIMESTAMP", "TIMESTAMP WITH TIME ZONE", "TIMESTAMP WITH LOCAL TIME ZONE", "INTERVAL YEAR TO MONTH", "INTERVAL DAY TO SECOND",
            "CLOB", "NCLOB", "BLOB",  "BFILE",
            "ROWID", "UROWID"
    );

    private static Map<String, Integer> dataType2SqlType = Maps.newHashMap();
    private static Map<Integer, String> sqlType2DataType = Maps.newHashMap();

    static {
        // init dataType2SqlType
        dataType2SqlType.put("CHAR", Types.CHAR);
        dataType2SqlType.put("NCHAR", Types.NCHAR);
        dataType2SqlType.put("VARCHAR2", Types.VARCHAR);
        dataType2SqlType.put("NVARCHAR2", Types.NVARCHAR);

        dataType2SqlType.put("NUMBER", Types.NUMERIC);
        dataType2SqlType.put("INTEGER", Types.INTEGER);
        dataType2SqlType.put("FLOAT", Types.DOUBLE);
        dataType2SqlType.put("BINARY_FLOAT", Types.FLOAT);
        dataType2SqlType.put("BINARY_DOUBLE", Types.DOUBLE);

        dataType2SqlType.put("LONG", Types.LONGVARCHAR);
        dataType2SqlType.put("LONG RAW", Types.BLOB);
        dataType2SqlType.put("RAW", Types.BLOB);

        dataType2SqlType.put("DATE", Types.DATE);
        dataType2SqlType.put("TIMESTAMP", Types.TIMESTAMP);
        dataType2SqlType.put("INTERVAL", ExtTypes.INTERVAL);

        //oracle里TIMERSTAMP类型查出来是TIMESTAMP(6)
        dataType2SqlType.put("TIMESTAMP(6)", Types.TIMESTAMP);
        dataType2SqlType.put("TIMESTAMP(8)", Types.TIMESTAMP);

        dataType2SqlType.put("TIMESTAMP WITH TIME ZONE", Types.TIMESTAMP_WITH_TIMEZONE);
        dataType2SqlType.put("TIMESTAMP WITH LOCAL TIME ZONE", Types.TIMESTAMP_WITH_TIMEZONE);
        dataType2SqlType.put("INTERVAL YEAR TO MONTH", Types.NULL); // TODO
        dataType2SqlType.put("INTERVAL DAY TO SECOND", Types.NULL); // TODO

        dataType2SqlType.put("CLOB", Types.CLOB);
        dataType2SqlType.put("NCLOB", Types.NCLOB);
        dataType2SqlType.put("BLOB", Types.BLOB);
        dataType2SqlType.put("BFILE", Types.BLOB); // TODO

        dataType2SqlType.put("ROWID", Types.ROWID);
        dataType2SqlType.put("UROWID", Types.ROWID);
        dataType2SqlType.put("XMLTYPE", ExtTypes.XMLTYPE);


        // init sqlType2DataType
        sqlType2DataType.put(Types.CHAR, "CHAR");
        sqlType2DataType.put(Types.NCHAR, "NCHAR");
        sqlType2DataType.put(Types.VARCHAR, "VARCHAR2");
        sqlType2DataType.put(Types.NVARCHAR, "NVARCHAR2");

        sqlType2DataType.put(Types.NUMERIC, "NUMBER");
        sqlType2DataType.put(Types.INTEGER, "INTEGER");
        sqlType2DataType.put(Types.FLOAT, "FLOAT");
//        sqlType2DataType.put("BINARY_FLOAT", Types.FLOAT);
        sqlType2DataType.put(Types.DOUBLE, "BINARY_DOUBLE");

//        sqlType2DataType.put("LONG", Types.BLOB);
//        sqlType2DataType.put("LONG RAW", Types.BLOB);
//        sqlType2DataType.put("RAW", Types.CHAR);

        sqlType2DataType.put(Types.DATE, "DATE");
        sqlType2DataType.put(Types.TIMESTAMP, "TIMESTAMP");
        sqlType2DataType.put(Types.TIMESTAMP_WITH_TIMEZONE, "TIMESTAMP WITH TIME ZONE");
//        sqlType2DataType.put(Types.TIMESTAMP_WITH_TIMEZONE, "TIMESTAMP WITH LOCAL TIME ZONE");
//        sqlType2DataType.put("INTERVAL YEAR TO MONTH", Types.NULL); // TODO
//        sqlType2DataType.put("INTERVAL DAY TO SECOND", Types.NULL); // TODO

        sqlType2DataType.put(Types.CLOB, "CLOB");
        sqlType2DataType.put(Types.NCLOB, "NCLOB");
        sqlType2DataType.put(Types.BLOB, "BLOB");
//        sqlType2DataType.put("BFILE", Types.BLOB); // TODO

        sqlType2DataType.put(Types.ROWID, "ROWID");
//        sqlType2DataType.put(Types.ROWID, "UROWID");

    }

    public MetaImpl(DataSourceDto dataSourceDto) {
        super(dataSourceDto);
    }

    @Override
    public List<String> getDataTypes() {
        return dataTypes;
    }

    @Override
    public Integer toSqlType(String dataType) {
        if (dataType.contains("TIME ZONE")) {
            return dataType2SqlType.getOrDefault("TIMESTAMP WITH TIME ZONE", null);
        }
        if (dataType.contains("TIMESTAMP")) {
            return dataType2SqlType.getOrDefault("TIMESTAMP", null);
        }
        if (dataType.contains("INTERVAL")) {
            return dataType2SqlType.getOrDefault("INTERVAL", null);
        }
        return dataType2SqlType.getOrDefault(dataType, null);
    }

    @Override
    public String toDataType(Integer sqlType) {
        return sqlType2DataType.getOrDefault(sqlType, null);
    }

    @Override
    public String wrap(String name, boolean preserveCase) {
        return StringUtils.wrap(name, "\"");
    }

    @Override
    public String characterSet(String database) {
        String sql = "select value from NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET'";
        String characterSet = query(sql, new ScalarHandler<String>());
        return characterSet;
    }

    @Override
    public Snapshot snapshot() {
        String sql = "select CURRENT_SCN scn from V$DATABASE";
        Map<String, Object> map = query(sql, new MapHandler());
        Snapshot snapshot = Snapshot.builder()
                .dsId(dataSourceDto.getId())
                .createdTime(new Date())
                .info(JSON.toJSONString(map))
                .build();
        return snapshot;
    }

    @Override
    public boolean valid(Snapshot snapshot) {
        String sql = "SELECT scn_to_timestamp(?) FROM DUAL";
        Map<String, Object> map = JSON.parseObject(snapshot.getInfo(), Map.class);
        if (MapUtils.isNotEmpty(map)) {
            Object obj = map.get("SCN");
            if (ObjectUtils.isNotEmpty(obj)) {
                String scn = obj.toString();
                try {
                    query(sql, new MapHandler(), scn);
                } catch (Exception e) {
                    return false;
                }
                return true;
            }
        }
        return false;
    }

    public static void sysPrivilege2String(UserDto.SysPrivDto userSysPrivDto, RoleDto.SysPrivDto roleSysPrivDto, List<String> privileges){
        if (userSysPrivDto != null && roleSysPrivDto == null){
            for (String privilege:privileges){
                switch (privilege){
                    case "CREATE USER":
                        userSysPrivDto.setCreatRolePriv("Y");
                        break;
                    case "CREATE PLUGGABLE DATABASE":
                        userSysPrivDto.setCreatDbPriv("Y");
                        break;
                }
            }
        } else if (roleSysPrivDto != null && userSysPrivDto == null){
            for (String privilege:privileges){
                switch (privilege){
                    case "CREATE USER":
                        roleSysPrivDto.setCreatRolePriv("Y");
                        break;
                    case "CREATE PLUGGABLE DATABASE":
                        roleSysPrivDto.setCreatDbPriv("Y");
                        break;
                }
            }
        }
    }

    public static void databasePrivilege2String(UserDto.DatabasePrivDto userDatabasePrivDto,RoleDto.DatabasePrivDto roleDatabasePrivDto ,List<String> privileges){
        if (userDatabasePrivDto!=null && roleDatabasePrivDto==null){
            for (String privilege:privileges){
                switch (privilege){
                    case "SELECT":
                        userDatabasePrivDto.setSelectPriv("Y");
                        break;
                    case "INSERT":
                        userDatabasePrivDto.setInsertPriv("Y");
                        break;
                    case "UPDATE":
                        userDatabasePrivDto.setUpdatePriv("Y");
                        break;
                    case "DELETE":
                        userDatabasePrivDto.setDeletePriv("Y");
                        break;
                    case "CREATE":
                        userDatabasePrivDto.setCreatePriv("Y");
                        break;
                    case "REFERECES":
                        userDatabasePrivDto.setReferencesPriv("Y");
                        break;
                    case "EXECUTE":
                        userDatabasePrivDto.setExecutePriv("Y");
                        break;
                    case "TRIGGER":
                        userDatabasePrivDto.setTriggerPriv("Y");
                        break;
                }
            }
        } else if (roleDatabasePrivDto!=null && userDatabasePrivDto==null) {
            for (String privilege : privileges) {
                switch (privilege) {
                    case "SELECT":
                        roleDatabasePrivDto.setSelectPriv("Y");
                        break;
                    case "INSERT":
                        roleDatabasePrivDto.setInsertPriv("Y");
                        break;
                    case "UPDATE":
                        roleDatabasePrivDto.setUpdatePriv("Y");
                        break;
                    case "DELETE":
                        roleDatabasePrivDto.setDeletePriv("Y");
                        break;
                    case "CREATE":
                        roleDatabasePrivDto.setCreatePriv("Y");
                        break;
                    case "REFERECES":
                        roleDatabasePrivDto.setReferencesPriv("Y");
                        break;
                    case "EXECUTE":
                        roleDatabasePrivDto.setExecutePriv("Y");
                        break;
                    case "TRIGGER":
                        roleDatabasePrivDto.setTriggerPriv("Y");
                        break;
                }
            }
        }
    }

    public static void tablePrivilege2String(UserDto.TablePrivDto userTablePrivDto, RoleDto.TablePrivDto roleTablePrivDto,List<String> privileges){
        if (userTablePrivDto!=null && roleTablePrivDto == null){
            for (String privilege:privileges){
                switch (privilege){
                    case "SELECT":
                        userTablePrivDto.setSelectPriv("Y");
                        break;
                    case "INSERT":
                        userTablePrivDto.setInsertPriv("Y");
                        break;
                    case "UPDATE":
                        userTablePrivDto.setUpdatePriv("Y");
                        break;
                    case "DELETE":
                        userTablePrivDto.setDeletePriv("Y");
                        break;
                    case "REFERECES":
                        userTablePrivDto.setReferencesPriv("Y");
                        break;
                    case "TRUNCATE":
                        userTablePrivDto.setTruncatePriv("Y");
                        break;
                    case "TRIGGER":
                        userTablePrivDto.setTriggerPriv("Y");
                        break;
                }
            }
        } else if (roleTablePrivDto!=null && userTablePrivDto == null){
            for (String privilege:privileges){
                switch (privilege){
                    case "SELECT":
                        roleTablePrivDto.setSelectPriv("Y");
                        break;
                    case "INSERT":
                        roleTablePrivDto.setInsertPriv("Y");
                        break;
                    case "UPDATE":
                        roleTablePrivDto.setUpdatePriv("Y");
                        break;
                    case "DELETE":
                        roleTablePrivDto.setDeletePriv("Y");
                        break;
                    case "REFERECES":
                        roleTablePrivDto.setReferencesPriv("Y");
                        break;
                    case "TRUNCATE":
                        roleTablePrivDto.setTruncatePriv("Y");
                        break;
                    case "TRIGGER":
                        roleTablePrivDto.setTriggerPriv("Y");
                        break;
                }
            }
        }
    }

    public static void columnPrivilege2String(UserDto.ColumnPrivDto userColumnPrivDto,
                                              RoleDto.ColumnPrivDto roleColumnPrivDto,
                                              List<String> privileges){
        if (userColumnPrivDto!=null && roleColumnPrivDto == null){
            for (String privilege:privileges){
                switch (privilege){
                    case "SELECT":
                        userColumnPrivDto.setSelectPriv("Y");
                        break;
                    case "INSERT":
                        userColumnPrivDto.setInsertPriv("Y");
                        break;
                    case "UPDATE":
                        userColumnPrivDto.setUpdatePriv("Y");
                        break;
                    case "DELETE":
                        userColumnPrivDto.setDeletePriv("Y");
                        break;
                    case "REFERECES":
                        userColumnPrivDto.setReferencesPriv("Y");
                        break;
                }
            }
        } else if (roleColumnPrivDto!=null && userColumnPrivDto == null){
            for (String privilege:privileges){
                switch (privilege){
                    case "SELECT":
                        roleColumnPrivDto.setSelectPriv("Y");
                        break;
                    case "INSERT":
                        roleColumnPrivDto.setInsertPriv("Y");
                        break;
                    case "UPDATE":
                        roleColumnPrivDto.setUpdatePriv("Y");
                        break;
                    case "DELETE":
                        roleColumnPrivDto.setDeletePriv("Y");
                        break;
                    case "REFERECES":
                        roleColumnPrivDto.setReferencesPriv("Y");
                        break;
                }
            }
        }

    }
}
